package flex.cc.util;

import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.net.URLEncoder;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.PropertyUtilsBean;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import flex.cc.PublicUtil;
import flex.cc.store.dto.entity.StoreActivityConfigEntity;
import flex.cc.store.dto.entity.StorePackageConfigEntity;

public class ExportUtil {
	private static final Logger LOGGER = LoggerFactory.getLogger(ExportUtil.class);
	
	public static final SimpleDateFormat SHORT_DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");

	public static final SimpleDateFormat LONG_DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    public static final int sheetMaxCount = 1000000;//单个sheet最多写入行数，2007版以上Excel最大单页104万行，此处设置100万自动分页

    private static final int columnWidth = 30;

//    public static void main(String args[]) {
//    	
//    	createExcel2();
//    }
    
    /**
     * 单表头用例
     */
//    public static void createExcel1() {
//    	System.out.println("开始");
//    	Workbook wb = createWorkbook(1000, false);
//    	String title = "测试报表";
//    	String[] headers = {"表头1","表头2","表头3","表头4","表头5"}; 
//    	String[] fields = {"a1","a2","a3","a4","a5"};
//    	int startRow = 0;
//    	List<CommonDataVo> dataList = new ArrayList<CommonDataVo>();
//    	for(int i = 0; i < 100; i++) {
//    		CommonDataVo data = new CommonDataVo();
//    		data.setA1("A1-"+i);
//    		data.setA2("A2-"+i);
//    		data.setA3("A3-"+i);
//    		data.setA4("A4-"+i);
//    		data.setA5("A5-"+i);
//    		dataList.add(data);
//    	}
//    	
//    	FileOutputStream fos = null;
//    	try {
//			exportExcel(title, headers, fields, startRow, wb, dataList);
//			
//			File file = new File("E:\\app\\sinosoft\\报表测试.xlsx");
//            fos = new FileOutputStream(file);
//            wb.write(fos);// 写文件
//		} catch (IOException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}finally {
//			try {
//				fos.close();
//			} catch (IOException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//		}
//    	System.out.println("结束");
//    }
//    
//    /**
//     * 双层表头用例
//     */
//    public static void createExcel2() {
//    	System.out.println("开始");
//    	Workbook wb = createWorkbook(1000, false);
//    	String title = "测试报表";
//    	String[] head0 = {"一层表头1","","一层表头2","",""};
//    	String[] headnum0 = {"1,1,0,1","1,1,2,4"};
//    	String[] head1 = {"表头1","表头2","表头3","表头4","表头5"}; 
//    	String[] fields = {"a1","a2","a3","a4","a5"};
//    	int startCell = 0;
//    	int endCell = 4;
//    	int startRow = 0;
//    	List<CommonDataVo> dataList = new ArrayList<CommonDataVo>();
//    	for(int i = 0; i < 100; i++) {
//    		CommonDataVo data = new CommonDataVo();
//    		data.setA1("A1-"+i);
//    		data.setA2("A2-"+i);
//    		data.setA3("A3-"+i);
//    		data.setA4("A4-"+i);
//    		data.setA5("A5-"+i);
//    		dataList.add(data);
//    	}
//    	
//    	FileOutputStream fos = null;
//    	try {
//			exportDoubleDeskHeaderExcel(title, head0, headnum0, head1, startCell, endCell, fields, startRow, wb, dataList);
//			File file = new File("E:\\app\\sinosoft\\报表测试双层表头.xlsx");
//            fos = new FileOutputStream(file);
//            wb.write(fos);// 写文件
//		} catch (Exception e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}finally {
//			try {
//				fos.close();
//			} catch (IOException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//		}
//    	System.out.println("结束");
//    }
    
    /**
     * 根据ResultSet导出Excel
     * @param title
     * @param headers
     * @param fields
     * @param startRow
     * @param wb
     * @param rs
     * @throws Exception
     */
    public static <T> void exportExcel(String title, String[] headers, String[] fields, int startRow, Workbook wb, ResultSet rs) throws Exception {

        Sheet sheet = null;
        startRow = startRow>0?startRow+2:startRow;
        int index = startRow, pageRowNo = startRow, columnCount = headers.length; // 行号、页码、列数
        Row nRow = null;
        ResultSetMetaData rsm = rs.getMetaData();
        int rsColumnCount = rsm.getColumnCount();// ResultSet列数
        Map<String, Object> obj_map = new HashMap<String, Object>(0);

        while(rs.next()) {//遍历游标
        	for (int i = 1; i <= rsColumnCount; i++) {//转换rs到map便于提取
				obj_map.put(rsm.getColumnName(i).toLowerCase(), rs.getObject(i));
			}
            int sheetIndex = index/sheetMaxCount;
            if (index % sheetMaxCount == 0) {
                sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
                sheet = wb.getSheetAt(sheetIndex);
                sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
                pageRowNo = 2;
                createHeader(sheet, title, headers);
            }else{
                sheet = wb.getSheetAt(sheetIndex);
            }
            index++;
            nRow = sheet.createRow(pageRowNo++); // 新建行对象

            for (int j = 0; j < columnCount; j++) {//正式写入字段，梳理顺序为传入字段顺序
                setCellValue(sheet, nRow.createCell(j), obj_map.get(fields[j].toLowerCase()));
            }
            obj_map.clear();//清空map待用
        }

    }

    /**
     * 根据List数据导出excel
     * @param title
     * @param headers
     * @param fields
     * @param startRow
     * @param wb
     * @param data
     * @throws IOException
     */
    @Deprecated
    public static <T> void exportExcel(String title, String[] headers, String[] fields, int startRow, Workbook wb, List<T> data) throws IOException {
    	if (data.size()>20000) {
    		LOGGER.info("警告：导出Excel时过多装载List数据，这样可能造成JVM内存溢出！！！");
		}
        Sheet sheet = null;
        startRow = startRow>0?startRow+2:startRow;
        int index = startRow, pageRowNo = startRow, columnCount = headers.length; // 行号、页码、列数
        Row nRow = null;

        if(!PublicUtil.isEmpty(data)) {
	        for (T obj : data) {
	            int sheetIndex = index/sheetMaxCount;
	            if (index % sheetMaxCount == 0) {
	                sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
	                sheet = wb.getSheetAt(sheetIndex);
	                sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
	                pageRowNo = 2;
	                createHeader(sheet, title, headers);
	            }else{
	                sheet = wb.getSheetAt(sheetIndex);
	            }
	            index++;
	            @SuppressWarnings("unchecked")
	            Map<String, Object> map = obj instanceof Map ? (Map<String, Object>) obj : beanToMap(obj);
	            nRow = sheet.createRow(pageRowNo++); // 新建行对象
	            for (int j = 0; j < columnCount; j++) {
	            	setCellValue(sheet, nRow.createCell(j) , map.get(fields[j]));
	            }
	        }
        }else {
        	sheet = wb.createSheet(title + "_" + (1));
        }

    }

    /**
     * 根据List数据导出excel，智能回访用
     * @param title
     * @param headers
     * @param fields
     * @param startRow
     * @param wb
     * @param data
     * @throws IOException
     */
    @Deprecated
    public static <T> void exportExcel2(String title, String[] headers, String[] fields, int startRow, Workbook wb, List<T> data) throws IOException {
    	if (data.size()>20000) {
    		LOGGER.info("警告：导出Excel时过多装载List数据，这样可能造成JVM内存溢出！！！");
		}
        Sheet sheet = null;
        startRow = startRow>0?startRow+2:startRow;
        int index = startRow, pageRowNo = startRow, columnCount = headers.length; // 行号、页码、列数
        Row nRow = null;

        if(!PublicUtil.isEmpty(data)) {
	        for (T obj : data) {
	            int sheetIndex = index/sheetMaxCount;
	            if (index % sheetMaxCount == 0) {
	                sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
	                sheet = wb.getSheetAt(sheetIndex);
	                sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
	                pageRowNo = 2;
	                createHeader(sheet, title, headers);
	            }else{
	                sheet = wb.getSheetAt(sheetIndex);
	            }
	            index++;
	            @SuppressWarnings("unchecked")
	            Map<String, Object> map = obj instanceof Map ? (Map<String, Object>) obj : beanToMap(obj);
	            nRow = sheet.createRow(pageRowNo++); // 新建行对象
	            for (int j = 0; j < columnCount; j++) {
	            	setCellValue2(sheet, nRow.createCell(j) , map.get(fields[j]),wb,fields[j]);
	            }
	        }
        }else {
        	sheet = wb.createSheet(title + "_" + (1));
        }

    }
    /**
     * 根据List数据导出(双层表头)excel
     * @param title
     * @param head0
     * @param headnum0
     * @param startRow
     * @param wb
     * @param data
     * @throws IOException
     */
    @Deprecated
    public static <T> void exportDoubleDeskHeaderListExcel(String title, String[] head0, String[] headnum0,String[] head1,Integer startCell,Integer endCell, int startRow, Workbook wb, List<List<String>> data) throws IOException {
    	if (data.size()>20000) {
    		LOGGER.info("警告：导出Excel时过多装载List数据，这样可能造成JVM内存溢出！！！");
		}
        Sheet sheet = null;
        startRow = startRow>0?startRow+2:startRow;
        int index = startRow, pageRowNo = startRow, columnCount = head0.length; // 行号、页码、列数
        Row nRow = null;

        if(!PublicUtil.isEmpty(data)) {
	        for (List obj : data) {
	            int sheetIndex = index/sheetMaxCount;
	            if (index % sheetMaxCount == 0) {
	                sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
	                sheet = wb.getSheetAt(sheetIndex);
	                sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
	                pageRowNo = 3;
	                createDoubleHeader(sheet, title, head0,headnum0,head1,startCell,endCell);
	            }else{
	                sheet = wb.getSheetAt(sheetIndex);
	            }
	            index++;
	            /*@SuppressWarnings("unchecked")
	            Map<String, Object> map = obj instanceof Map ? (Map<String, Object>) obj : beanToMap(obj);  */

	            nRow = sheet.createRow(pageRowNo++); // 新建行对象
	            for (int j = 0; j < columnCount; j++) {
	                setCellValue(sheet, nRow.createCell(j),obj.get(j));
	            }
	        }
        }else {
        	sheet = wb.createSheet(title + "_" + (1));
        }

    }



    /**
     * 根据List数据导出(双层表头)excel
     * @param title
     * @param headers
     * @param fields
     * @param startRow
     * @param wb
     * @param data
     * @throws IOException
     */
    @Deprecated
    public static <T> void exportDoubleDeskHeaderExcel(String title, String[] head0, String[] headnum0,String[] head1,Integer startCell,Integer endCell,String[] fields, int startRow, Workbook wb, List<T> data) throws IOException {
    	if (data.size()>20000) {
    		LOGGER.info("警告：导出Excel时过多装载List数据，这样可能造成JVM内存溢出！！！");
		}
        Sheet sheet = null;
        startRow = startRow>0?startRow+2:startRow;
        int index = startRow, pageRowNo = startRow, columnCount = head0.length; // 行号、页码、列数
        Row nRow = null;

        if(!PublicUtil.isEmpty(data)) {
	        for (T obj : data) {
	            int sheetIndex = index/sheetMaxCount;
	            if (index % sheetMaxCount == 0) {
	                sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
	                sheet = wb.getSheetAt(sheetIndex);
	                sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
	                pageRowNo = 3;
	                createDoubleHeader(sheet, title, head0,headnum0,head1,startCell,endCell);
	            }else{
	                sheet = wb.getSheetAt(sheetIndex);
	            }
	            index++;
	            @SuppressWarnings("unchecked")
	            Map<String, Object> map = obj instanceof Map ? (Map<String, Object>) obj : beanToMap(obj);
	            nRow = sheet.createRow(pageRowNo++); // 新建行对象
	            for (int j = 0; j < columnCount; j++) {
	                setCellValue(sheet, nRow.createCell(j), map.get(fields[j]));
	            }
	        }
        }else {
        	sheet = wb.createSheet(title + "_" + (1));
        }

    }

    /**
     * responseWorkbook
     * @param title
     * @param wb
     * @param request
     * @param response
     * @throws IOException
     */
    public static void responseWorkbook(String title, Workbook wb,HttpServletRequest request, HttpServletResponse response)throws IOException{
        String sFileName = title + ".xlsx";
        // 火狐浏览器导出excel乱码
        String agent = request.getHeader("User-Agent");
        // 判断是否火狐浏览器
        boolean isFirefox = agent != null && agent.contains("Firefox");
        if (isFirefox) {
            sFileName = new String(sFileName.getBytes("UTF-8"), "ISO-8859-1");
        } else {
            sFileName = URLEncoder.encode(sFileName, "UTF8");
        }
        response.setHeader("Content-Disposition", "attachment; filename=".concat(sFileName));
        response.setHeader("Connection", "close");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        wb.write(response.getOutputStream());
    }
    /**
     * 设置单元格的值
     * @param cell
     * @param cellVal
     */
    private static synchronized void setCellValue(Sheet sheet, Cell cell, Object cellVal){
        if (cellVal instanceof String) {
            cell.setCellValue((String) cellVal);
            cell.setCellType(CellType.STRING);
        } else if (cellVal instanceof Date) {
            cell.setCellValue((Date) cellVal);
            cell.setCellType(CellType.STRING);
        } else if (cellVal instanceof Boolean) {
            cell.setCellValue((Boolean) cellVal);
            cell.setCellType(CellType.BOOLEAN);
        } else if (cellVal instanceof Double) {
            cell.setCellValue((Double) cellVal);
            cell.setCellType(CellType.NUMERIC);
        } else if (cellVal instanceof Calendar) {
            cell.setCellValue((Calendar) cellVal);
            cell.setCellType(CellType.STRING);
        } else if (cellVal instanceof RichTextString) {
            cell.setCellValue((RichTextString) cellVal);
            cell.setCellType(CellType.STRING);
        } else {
            cell.setCellValue(String.valueOf(cellVal));
            cell.setCellType(CellType.STRING);
        }
        cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 3));
    }

    /**
     * 智能回访设置单元格的值
     * @param sheet
     * @param cell
     * @param cellVal
     * @param wb
     * @param key
     */
    private static synchronized void setCellValue2(Sheet sheet, Cell cell, Object cellVal,Workbook wb,String key){
        if (cellVal instanceof String) {
            cell.setCellValue((String) cellVal);
            cell.setCellType(CellType.STRING);
        } else if (cellVal instanceof Date) {
            cell.setCellValue((Date) cellVal);
            cell.setCellType(CellType.STRING);
        } else if (cellVal instanceof Boolean) {
            cell.setCellValue((Boolean) cellVal);
            cell.setCellType(CellType.BOOLEAN);
        } else if (cellVal instanceof Double) {
            cell.setCellValue((Double) cellVal);
            cell.setCellType(CellType.NUMERIC);
        } else if (cellVal instanceof Calendar) {
            cell.setCellValue((Calendar) cellVal);
            cell.setCellType(CellType.STRING);
        } else if (cellVal instanceof RichTextString) {
            cell.setCellValue((RichTextString) cellVal);
            cell.setCellType(CellType.STRING);
        } else {
            cell.setCellValue(String.valueOf(cellVal));
            cell.setCellType(CellType.STRING);
        }
        CellStyle style = wb.createCellStyle();
        cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 3));
        if("questionID".equals(key) || "question".equals(key)|| "asrResult".equals(key)|| "issResult".equals(key)||"questionStd".equals(key)) {
            style.setWrapText(true); //设置换行
            style.setBorderBottom(BorderStyle.THIN); //下边框
            style.setBorderLeft(BorderStyle.THIN);//左边框
            style.setBorderTop(BorderStyle.THIN);//上边框
            style.setBorderRight(BorderStyle.THIN);//右边框
        	cell.setCellStyle(style);
        }
        style.setAlignment(HorizontalAlignment.CENTER);
    }

    /**
     * JavaBean转Map
     *
     * @param obj
     * @return
     */
    public static Map<String, Object> beanToMap(Object obj) {
        Map<String, Object> params = new HashMap<String, Object>(0);
        try {
            PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
            PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(obj);
            for (int i = 0; i < descriptors.length; i++) {
                String name = descriptors[i].getName();
                if (!StringUtils.equals(name, "class")) {
                     try {
                         params.put(name, propertyUtilsBean.getNestedProperty(obj, name));
                     }catch (NoSuchMethodException noSuchMethodException){
                         continue;
                     }
                }
            }
        } catch (Exception e) {
            LOGGER.error("URLDecoder fail :", e);
        }
        return params;
    }

    /**
     * 创建表头
     * @param sheet
     * @param headers
     */
    private static void createHeader(Sheet sheet, String title, String[] headers){

        //设置标题
        Row tRow = sheet.createRow(0);
        Cell hc = tRow.createCell(0);
        hc.setCellValue(new XSSFRichTextString(title));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));// 合并标题行：起始行号，终止行号， 起始列号，终止列号
        hc.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 1));

        //设置表头
        Row nRow = sheet.createRow(1);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = nRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));
        }
    }


    /**
     * 创建双层表头
     * @param sheet
     * @param headers
     */
    private static void createDoubleHeader(Sheet sheet, String title, String[] head0, String[] headnum0,String[] head1,Integer startCell,Integer endCell){
        //设置标题
        Row tRow = sheet.createRow(0);
        Cell hc = tRow.createCell(0);
        hc.setCellValue(new XSSFRichTextString(title));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, head0.length-1));// 合并标题行：起始行号，终止行号， 起始列号，终止列号
        hc.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 1));

        //第一行表头列名
        Row nRow = sheet.createRow(1);
        for (int i = 0; i < head0.length; i++) {
        	Cell cell = nRow.createCell(i);
            cell = nRow.createCell(i);
            cell.setCellValue(head0[i]);
            cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));
        }

        //第二行表头列名
        Row row = sheet.createRow(2);
        for (int i = 0; i < head0.length; i++) {
            if(i >= startCell && i< endCell) {
                for (int j = 0; j < head1.length; j++) {
                	Cell cell = row.createCell(i+j);
                    cell.setCellValue(head1[j]);
                    cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));
                }
                break;
            }
            Cell cell = row.createCell(i);
            cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));
        }

        //动态合并单元格
        for (int i = 0; i < headnum0.length; i++) {
            String[] temp = headnum0[i].split(",");
            Integer startrow = Integer.parseInt(temp[0]);
            Integer overrow = Integer.parseInt(temp[1]);
            Integer startcol = Integer.parseInt(temp[2]);
            Integer overcol = Integer.parseInt(temp[3]);
            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
                    startcol, overcol));
        }

    }


    /**
     * 根据List数据导出excel，回访模块使用
     * mf 20190115
     * @param title 标题
     * @param headers
     * @param columnCount  数据的列数
     * @param data
     * @throws IOException
     */
    @Deprecated
    public static <T> void exportExcelForVisit(Workbook wb, String title, List<List<String>> headers, List<String> headerMultile, List<List<Object>> data) throws IOException {
    	if (data.size()>20000) {
    		LOGGER.info("警告：导出Excel时过多装载List数据，这样可能造成JVM内存溢出！！！");
		}
        Sheet sheet = null;
        int index = 0, dataRowNo = headers.size()+1, columnCount=data.size()>0?data.get(0).size():0; // 行号、数据行号、总列数
        Row nRow = null;

        if(!PublicUtil.isEmpty(data)) {
	        for (List<Object> objs: data) {//遍历每一条数据
	            int sheetIndex = index/sheetMaxCount;
	            if (index % sheetMaxCount == 0) {
	                sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
	                sheet = wb.getSheetAt(sheetIndex);
	                sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
	                createMultilevelHeader(sheet, title, columnCount, headers, headerMultile);
	            }else{
	                sheet = wb.getSheetAt(sheetIndex);
	            }
	            index++;
	            nRow = sheet.createRow(dataRowNo++); // 新建行对象
	            for (int j = 0; j < columnCount; j++) {
	            	setCellValue(sheet, nRow.createCell(j), objs.get(j));
	            }
	        }
        }else {
        	sheet = wb.createSheet(title + "_" + (1));
        }
    }

    /**
     * 创建多级表头，可以是一到n级
     * mf 20190115
     * @param sheet
     * @param headers
     */
    private static void createMultilevelHeader(Sheet sheet, String title, int columnCount, List<List<String>> headers, List<String> headerMultil){
        //设置标题
        Row tRow = sheet.createRow(0);
        Cell hc = tRow.createCell(0);
        hc.setCellValue(new XSSFRichTextString(title));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnCount-1));// 合并标题行：起始行号，终止行号， 起始列号，终止列号
        hc.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 1));

        //创建表头对应的所有单元格
        for (int i = 0; i < headers.size(); i++) {
        	Row nRow = sheet.createRow(i+1);
        	for (int j = 0; j < columnCount; j++) {
        		Cell cell = nRow.createCell(j);
        		cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));
        	}
        }
        //动态合并表头的单元格
        Map<String,Integer> colMap=new HashMap<>();
        Map<String,Integer> rowMap=new HashMap<>();
        if(null != headerMultil) {
        	for (int i = 0; i < headerMultil.size(); i++) {
                String[] temp = headerMultil.get(i).split(",");
                Integer startrow = Integer.parseInt(temp[0]);
                Integer overrow = Integer.parseInt(temp[1]);
                Integer startcol = Integer.parseInt(temp[2]);
                Integer overcol = Integer.parseInt(temp[3]);
                sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,startcol, overcol));
                if(startcol!=overcol) {//列进行过合并
                	colMap.put(startrow+"-"+startcol, overcol);
                }
                if(startrow!=overrow) {//行进行过合并
                	rowMap.put(overrow+"-"+startcol, overcol);
                }
            }

        }
        //给表头的单元格赋值
        for (int i = 0; i < headers.size(); i++) {
        	Integer colIndex=0;
        	List<String> head=headers.get(i);
        	Row row = sheet.getRow(i+1);
            for (int j = 0; j < head.size(); j++) {
            	if(null != rowMap.get((i+1)+"-"+colIndex)) {//该单元格进行过行合并则不赋值
            		colIndex++;
            		j--;
            		continue;
            	}
            	Cell cell =row.getCell(colIndex);
            	if(null != cell) {
            		cell.setCellValue(head.get(j));
                	if(null != colMap.get((i+1)+"-"+colIndex)) {//该单元格进行过列合并更新下一个单元格的开始列下标
                		colIndex=colMap.get((i+1)+"-"+colIndex)+1;
                	}else {
                		colIndex++;
                	}
            	}
            }
        }
    }

    /**
     * 根据List数据导出excel，回访模块使用
     * xqg 20190329
     * @param title 标题
     * @param headers
     * @param columnCount  数据的列数
     * @param data
     * @throws IOException
     */
    @Deprecated
    public static <T> void exportExcelIntelligent(Workbook wb, String title, List<List<String>> headers, List<String> headerMultile, List<List<Object>> data) throws IOException {
    	if (data.size()>20000) {
    		LOGGER.info("警告：导出Excel时过多装载List数据，这样可能造成JVM内存溢出！！！");
		}
        Sheet sheet = null;
        int index = 0, dataRowNo = headers.size()+1, columnCount=data.size()>0?data.get(0).size():0; // 行号、数据行号、总列数
        int headerSize = headers.get(0).size();
        Row nRow = null;

        if(!PublicUtil.isEmpty(data)) {
	        for (List<Object> objs: data) {//遍历每一条数据
	            int sheetIndex = index/sheetMaxCount;
	            if (index % sheetMaxCount == 0) {
	                sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
	                sheet = wb.getSheetAt(sheetIndex);
	                sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
	                createMultilevelHeader(sheet, title, headerSize, headers, headerMultile);
	            }else{
	                sheet = wb.getSheetAt(sheetIndex);
	            }
	            index++;
	            nRow = sheet.createRow(dataRowNo++); // 新建行对象
	            for (int j = 0; j < objs.size(); j++) {
	            //for (int j = 0; j < columnCount; j++) {
	            	setCellValue(sheet, nRow.createCell(j), objs.get(j));
	            }
	        }
        }else {
        	sheet = wb.createSheet(title + "_" + (1));
        }
    }


    /**
     * 创建Workbook
     * @param tmpNm SXSSFWorkbook缓冲区大小
     * @return
     */
    @Deprecated
    public static SXSSFWorkbook createWorkbook(int tmpNm){
    	if (tmpNm == 0) {
			tmpNm = 1000;//传0默认1000行缓冲
		}
    	SXSSFWorkbook wb = new SXSSFWorkbook(tmpNm);
        wb.setCompressTempFiles(true); //压缩临时文件，很重要，否则磁盘很快就会被写满
        CellStyle hcs = wb.createCellStyle();
        hcs.setBorderBottom(BorderStyle.THIN);
        hcs.setBorderLeft(BorderStyle.THIN);
        hcs.setBorderRight(BorderStyle.THIN);
        hcs.setBorderTop(BorderStyle.THIN);
        hcs.setAlignment(HorizontalAlignment.CENTER);
        Font hfont = wb.createFont();
        hfont.setFontName("宋体");
        hfont.setFontHeightInPoints((short) 16);// 设置字体大小
        //hfont.setBoldweight(Font.BOLDWEIGHT_BOLD);// 加粗
        hfont.setBold(true);
        hcs.setFont(hfont);

        CellStyle tcs = wb.createCellStyle();
        tcs.setBorderBottom(BorderStyle.THIN);
        tcs.setBorderLeft(BorderStyle.THIN);
        tcs.setBorderRight(BorderStyle.THIN);
        tcs.setBorderTop(BorderStyle.THIN);
        tcs.setAlignment(HorizontalAlignment.CENTER);
        Font tfont = wb.createFont();
        tfont.setFontName("宋体");
        tfont.setFontHeightInPoints((short) 12);// 设置字体大小
        tfont.setBold(true);
        tcs.setFont(tfont);

        CellStyle cs = wb.createCellStyle();
        cs.setBorderBottom(BorderStyle.THIN);
        cs.setBorderLeft(BorderStyle.THIN);
        cs.setBorderRight(BorderStyle.THIN);
        cs.setBorderTop(BorderStyle.THIN);
        cs.setAlignment(HorizontalAlignment.CENTER);
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);// 设置字体大小

        return wb;
    }






 /*=========================================加入创建人、创建时间、查询时间区间 （用于一级表头）=============================================================*/


    /**
     * 创建Workbook (包含创建人、创建时间、查询时间区间)
     * @param tmpNm SXSSFWorkbook缓冲区大小
     * @return
     */
    public static SXSSFWorkbook createWorkbook(int tmpNm,boolean flag){

    	if(!flag) {
    		return createWorkbook(tmpNm);//flag 为false执行
    	}

    	if (tmpNm == 0) {
    		tmpNm = 1000;//传0默认1000行缓冲
    	}
    	SXSSFWorkbook wb = new SXSSFWorkbook(tmpNm);
    	wb.setCompressTempFiles(true); //压缩临时文件，很重要，否则磁盘很快就会被写满
    	CellStyle hcs = wb.createCellStyle();
    	hcs.setBorderBottom(BorderStyle.THIN);
    	hcs.setBorderLeft(BorderStyle.NONE);
    	hcs.setBorderRight(BorderStyle.NONE);
    	hcs.setBorderTop(BorderStyle.NONE);
    	hcs.setAlignment(HorizontalAlignment.CENTER);
    	Font hfont = wb.createFont();
    	hfont.setFontName("宋体");
    	hfont.setFontHeightInPoints((short) 16);// 设置字体大小
    	//hfont.setBoldweight(Font.BOLDWEIGHT_BOLD);// 加粗
    	hfont.setBold(true);
    	hcs.setFont(hfont);



    	CellStyle h1 = wb.createCellStyle();
    	h1.setBorderBottom(BorderStyle.NONE);
    	h1.setBorderLeft(BorderStyle.NONE);
    	h1.setBorderRight(BorderStyle.THIN);
    	h1.setBorderTop(BorderStyle.NONE);
    	h1.setAlignment(HorizontalAlignment.LEFT);
    	Font font1 = wb.createFont();
    	font1.setFontName("宋体");
    	font1.setFontHeightInPoints((short) 10);// 设置字体大小
    	font1.setBold(true);
    	h1.setFont(font1);

    	CellStyle tcs = wb.createCellStyle();
    	tcs.setBorderBottom(BorderStyle.THIN);
    	tcs.setBorderLeft(BorderStyle.THIN);
    	tcs.setBorderRight(BorderStyle.THIN);
    	tcs.setBorderTop(BorderStyle.THIN);
    	tcs.setAlignment(HorizontalAlignment.CENTER);
    	tcs.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//设置背景色
    	tcs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    	Font tfont = wb.createFont();
    	tfont.setFontName("宋体");
    	tfont.setFontHeightInPoints((short) 12);// 设置字体大小
    	// tfont.setBoldweight(Font.BOLDWEIGHT_BOLD);// 加粗
    	tfont.setBold(true);
    	tcs.setFont(tfont);

    	CellStyle cs = wb.createCellStyle();
    	cs.setBorderBottom(BorderStyle.THIN);
    	cs.setBorderLeft(BorderStyle.THIN);
    	cs.setBorderRight(BorderStyle.THIN);
    	cs.setBorderTop(BorderStyle.THIN);
    	cs.setAlignment(HorizontalAlignment.CENTER);
    	Font font = wb.createFont();
    	font.setFontName("宋体");
    	font.setFontHeightInPoints((short) 12);// 设置字体大小

    	return wb;
    }


    /**
     * 根据List数据导出excel (包含创建人、创建时间、查询时间区间)
     * @param title
     * @param headers
     * @param fields
     * @param startRow
     * @param wb
     * @param data
     * @throws IOException
     */
    public static <T> void exportExcel(String title, String[] headers, String[] fields, int startRow, Workbook wb, List<T> data,ExportParamVo exportParamVo) throws IOException {
    	if (data.size()>20000) {
    		LOGGER.info("警告：导出Excel时过多装载List数据，这样可能造成JVM内存溢出！！！");
		}
        Sheet sheet = null;
        startRow = startRow>0?startRow+4:startRow;
        int index = startRow, pageRowNo = startRow, columnCount = headers.length; // 行号、页码、列数
        Row nRow = null;

        if(!PublicUtil.isEmpty(data)) {
	        for (T obj : data) {
	            int sheetIndex = index/sheetMaxCount;
	            if (index % sheetMaxCount == 0) {
	                sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
	                sheet = wb.getSheetAt(sheetIndex);
	                sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
	                pageRowNo = createHeader(sheet, title, headers,exportParamVo);
	            }else{
	                sheet = wb.getSheetAt(sheetIndex);
	            }
	            index++;
	            @SuppressWarnings("unchecked")
	            Map<String, Object> map = obj instanceof Map ? (Map<String, Object>) obj : beanToMap(obj);
	            nRow = sheet.createRow(pageRowNo++); // 新建行对象
	            for (int j = 0; j < columnCount; j++) {
	            	setCellValue(sheet, nRow.createCell(j) , map.get(fields[j]),(short)4);
	            }
	        }
        }else {
        	sheet = wb.createSheet(title + "_" + (1));
        }

    }



    /**
     * 设置单元格的值 (包含创建人、创建时间、查询时间区间)
     * @param cell
     * @param cellVal
     */
    public static synchronized void setCellValue(Sheet sheet, Cell cell, Object cellVal,short v){
        if (cellVal instanceof String) {
            cell.setCellValue((String) cellVal);
            cell.setCellType(CellType.STRING);
        } else if (cellVal instanceof Date) {
            cell.setCellValue((Date) cellVal);
            cell.setCellType(CellType.STRING);
        } else if (cellVal instanceof Boolean) {
            cell.setCellValue((Boolean) cellVal);
            cell.setCellType(CellType.BOOLEAN);
        } else if (cellVal instanceof Double) {
            cell.setCellValue((Double) cellVal);
            cell.setCellType(CellType.NUMERIC);
        } else if (cellVal instanceof Calendar) {
            cell.setCellValue((Calendar) cellVal);
            cell.setCellType(CellType.STRING);
        } else if (cellVal instanceof RichTextString) {
            cell.setCellValue((RichTextString) cellVal);
            cell.setCellType(CellType.STRING);
        } else {
            cell.setCellValue(String.valueOf(cellVal));
            cell.setCellType(CellType.STRING);
        }
        cell.setCellStyle(sheet.getWorkbook().getCellStyleAt(v));
    }


    /**
     * 创建表头 (包含创建人、创建时间、查询时间区间)
     * @param sheet
     * @param headers
     */
	@SuppressWarnings( "unused" )
	public static int createHeader(Sheet sheet, String title, String[] headers,ExportParamVo exportParamVo){

    	int row = 0;
    	sheet.setDefaultColumnWidth(columnWidth);
        //设置标题
        Row tRow = sheet.createRow(0);
        ++row;
        Cell hc = tRow.createCell(0);
        for(int i = 1; i < headers.length; i++) {
        	Cell cell = tRow.createCell(i);
        	CellStyle style = sheet.getWorkbook().getCellStyleAt((short) 1);
        	style.setBorderRight(BorderStyle.THIN);
        	cell.setCellStyle(style);
        }
        hc.setCellValue(new XSSFRichTextString(title));
        if(headers.length>1) {
        	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));// 合并标题行：起始行号，终止行号， 起始列号，终止列号
        }
        hc.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 1));

        //设置创建人、创建时间
        Row tRow1 = sheet.createRow(1);
        ++row;
        Cell hc1 = tRow1.createCell(0);
        for(int i = 1; i < headers.length; i++) {
        	Cell cell = tRow1.createCell(i);
        	if(i==headers.length-1)
        	cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));
        }
        hc1.setCellValue("创建人："+exportParamVo.getCreater()+"  创建时间："+exportParamVo.getCreateTime());
        if(headers.length>1) {
        	sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));// 合并标题行：起始行号，终止行号， 起始列号，终止列号
        }
        hc1.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));

        //设置查询时间区间
        Row tRow2 = sheet.createRow(2);
        ++row;
        Cell hc2 = tRow2.createCell(0);
        for(int i = 1; i < headers.length; i++) {
        	Cell cell = tRow2.createCell(i);
        	if(i==headers.length-1)
        	cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));
        }
        hc2.setCellValue("查询时间区间："+exportParamVo.getQueryStartTime()+" ~ "+exportParamVo.getQueryEndTime());
        if(headers.length>1) {
        	sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, headers.length - 1));// 合并标题行：起始行号，终止行号， 起始列号，终止列号
        }
        hc2.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));

        //设置表头
        Row nRow = sheet.createRow(3);
        ++row;
        for (int i = 0; i < headers.length; i++) {
            Cell cell = nRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 3));
        }

        return row;
    }

/*==================================================回访模块   加入创建人、创建时间、查询时间区间 （用于一级表头） ===============================================================================*/

    /**
     * 根据List数据导出excel，回访模块使用  (包含创建人、创建时间、查询时间区间)
     * mf 20190115
     * @param title 标题
     * @param headers
     * @param columnCount  数据的列数
     * @param data
     * @throws IOException
     */
    public static <T> void exportExcelForVisit(Workbook wb, String title, List<List<String>> headers, List<String> headerMultile, List<List<Object>> data,ExportParamVo exportParamVo) throws IOException {
    	if (data.size()>20000) {
    		LOGGER.info("警告：导出Excel时过多装载List数据，这样可能造成JVM内存溢出！！！");
		}


        Sheet sheet = null;
        int index = 0, dataRowNo = headers.size()+1, columnCount=data.size()>0?data.get(0).size():0; // 行号、数据行号、总列数
        Row nRow = null;

        if(!PublicUtil.isEmpty(data)) {
	        for (List<Object> objs: data) {//遍历每一条数据
	            int sheetIndex = index/sheetMaxCount;
	            if (index % sheetMaxCount == 0) {
	                sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
	                sheet = wb.getSheetAt(sheetIndex);
	                sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
	                dataRowNo = createMultilevelHeader(sheet, title, columnCount, headers, headerMultile,exportParamVo);
	            }else{
	                sheet = wb.getSheetAt(sheetIndex);
	            }
	            index++;
	            nRow = sheet.createRow(dataRowNo++); // 新建行对象
	            for (int j = 0; j < columnCount; j++) {
	            	setCellValue(sheet, nRow.createCell(j), objs.get(j),(short)4);
	            }
	        }
        }else {
        	sheet = wb.createSheet(title + "_" + (1));
        }
    }



    /**
     * 创建多级表头，可以是一到n级 (包含创建人、创建时间、查询时间区间)
     * mf 20190115
     * @param sheet
     * @param headers
     */
    private static int createMultilevelHeader(Sheet sheet, String title, int columnCount, List<List<String>> headers, List<String> headerMultil,ExportParamVo exportParamVo){

    	int dataRowNo = 0;
    	sheet.setDefaultColumnWidth(columnWidth);
    	//设置标题
        Row tRow = sheet.createRow(0);
        ++dataRowNo;
        Cell hc = tRow.createCell(0);
        for(int i = 1; i < columnCount; i++) {
        	Cell cell = tRow.createCell(i);
        	CellStyle style = sheet.getWorkbook().getCellStyleAt((short) 1);
        	style.setBorderRight(BorderStyle.THIN);
        	cell.setCellStyle(style);
        }
        hc.setCellValue(new XSSFRichTextString(title));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnCount-1));// 合并标题行：起始行号，终止行号， 起始列号，终止列号
        hc.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 1));

        //设置创建人、创建时间
        Row tRow1 = sheet.createRow(1);
        ++dataRowNo;
        Cell hc1 = tRow1.createCell(0);
        for(int i = 1; i < columnCount; i++) {
        	Cell cell = tRow1.createCell(i);
        	if(i==columnCount-1)
        	cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));
        }
        hc1.setCellValue("创建人："+exportParamVo.getCreater()+"  创建时间："+exportParamVo.getCreateTime());
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, columnCount - 1));// 合并标题行：起始行号，终止行号， 起始列号，终止列号
        hc1.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));

        //设置查询时间区间
        Row tRow2 = sheet.createRow(2);
        ++dataRowNo;
        Cell hc2 = tRow2.createCell(0);
        for(int i = 1; i < columnCount; i++) {
        	Cell cell = tRow2.createCell(i);
        	if(i==columnCount-1)
        	cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));
        }
        hc2.setCellValue("查询时间区间："+exportParamVo.getQueryStartTime()+" ~ "+exportParamVo.getQueryEndTime());
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, columnCount - 1));// 合并标题行：起始行号，终止行号， 起始列号，终止列号
        hc2.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));

        //创建表头对应的所有单元格
        for (int i = 0; i < headers.size(); i++) {
        	Row nRow = sheet.createRow(i+1+2);
        	++dataRowNo;
        	for (int j = 0; j < columnCount; j++) {
        		Cell cell = nRow.createCell(j);
        		cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 3));
        	}
        }
        //动态合并表头的单元格
        Map<String,Integer> colMap=new HashMap<>();
        Map<String,Integer> rowMap=new HashMap<>();
        if(null != headerMultil) {
        	for (int i = 0; i < headerMultil.size(); i++) {
                String[] temp = headerMultil.get(i).split(",");
                Integer startrow = Integer.parseInt(temp[0])+2;
                Integer overrow = Integer.parseInt(temp[1])+2;
                Integer startcol = Integer.parseInt(temp[2]);
                Integer overcol = Integer.parseInt(temp[3]);
                sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,startcol, overcol));
                if(startcol!=overcol) {//列进行过合并
                	colMap.put(startrow+"-"+startcol, overcol);
                }
                if(startrow!=overrow) {//行进行过合并
                	rowMap.put(overrow+"-"+startcol, overcol);
                }
            }

        }
        //给表头的单元格赋值
        for (int i = 0; i < headers.size(); i++) {
        	Integer colIndex=0;
        	List<String> head=headers.get(i);
        	Row row = sheet.getRow(i+1+2);
            for (int j = 0; j < head.size(); j++) {
            	if(null != rowMap.get((i+1+2)+"-"+colIndex)) {//该单元格进行过行合并则不赋值
            		colIndex++;
            		j--;
            		continue;
            	}
            	Cell cell =row.getCell(colIndex);
            	if(null != cell) {
            		cell.setCellValue(head.get(j));
                	if(null != colMap.get((i+1+2)+"-"+colIndex)) {//该单元格进行过列合并更新下一个单元格的开始列下标
                		colIndex=colMap.get((i+1+2)+"-"+colIndex)+1;
                	}else {
                		colIndex++;
                	}
            	}
            }
        }

        return dataRowNo;
    }

    /**
     * 根据List数据导出excel，智能回访用 (包含创建人、创建时间、查询时间区间)
     * @param title
     * @param headers
     * @param fields
     * @param startRow
     * @param wb
     * @param data
     * @throws IOException
     */
    public static <T> void exportExcel2(String title, String[] headers, String[] fields, int startRow, Workbook wb, List<T> data,ExportParamVo exportParamVo) throws IOException {
    	if (data.size()>20000) {
    		LOGGER.info("警告：导出Excel时过多装载List数据，这样可能造成JVM内存溢出！！！");
		}
        Sheet sheet = null;
        startRow = startRow>0?startRow+4:startRow;
        int index = startRow, pageRowNo = startRow, columnCount = headers.length; // 行号、页码、列数
        Row nRow = null;

        if(!PublicUtil.isEmpty(data)) {
        	CellStyle style = wb.createCellStyle();
	        for (T obj : data) {
	            int sheetIndex = index/sheetMaxCount;
	            if (index % sheetMaxCount == 0) {
	                sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
	                sheet = wb.getSheetAt(sheetIndex);
	                sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
	                pageRowNo = createHeader(sheet, title, headers, exportParamVo);
	            }else{
	                sheet = wb.getSheetAt(sheetIndex);
	            }
	            index++;
	            @SuppressWarnings("unchecked")
	            Map<String, Object> map = obj instanceof Map ? (Map<String, Object>) obj : beanToMap(obj);
	            nRow = sheet.createRow(pageRowNo++); // 新建行对象
	            for (int j = 0; j < columnCount; j++) {
	            	setCellValue2(sheet, nRow.createCell(j) , map.get(fields[j]),wb,fields[j],(short)4,style );
	            }
	        }
        }else {
        	sheet = wb.createSheet(title + "_" + (1));
        }

    }

    /**
     * 智能回访设置单元格的值(包含创建人、创建时间、查询时间区间)
     * @param sheet
     * @param cell
     * @param cellVal
     * @param wb
     * @param key
     */
    private static synchronized void setCellValue2(Sheet sheet, Cell cell, Object cellVal,Workbook wb,String key,short v,CellStyle style){
        if (cellVal instanceof String) {
            cell.setCellValue((String) cellVal);
            cell.setCellType(CellType.STRING);
        } else if (cellVal instanceof Date) {
            cell.setCellValue((Date) cellVal);
            cell.setCellType(CellType.STRING);
        } else if (cellVal instanceof Boolean) {
            cell.setCellValue((Boolean) cellVal);
            cell.setCellType(CellType.BOOLEAN);
        } else if (cellVal instanceof Double) {
            cell.setCellValue((Double) cellVal);
            cell.setCellType(CellType.NUMERIC);
        } else if (cellVal instanceof Calendar) {
            cell.setCellValue((Calendar) cellVal);
            cell.setCellType(CellType.STRING);
        } else if (cellVal instanceof RichTextString) {
            cell.setCellValue((RichTextString) cellVal);
            cell.setCellType(CellType.STRING);
        } else {
            cell.setCellValue(String.valueOf(cellVal));
            cell.setCellType(CellType.STRING);
        }

        cell.setCellStyle(sheet.getWorkbook().getCellStyleAt(v));
        if("questionID".equals(key) || "question".equals(key)|| "asrResult".equals(key)|| "issResult".equals(key)||"questionStd".equals(key)) {
            style.setWrapText(true); //设置换行
            style.setBorderBottom(BorderStyle.THIN); //下边框
            style.setBorderLeft(BorderStyle.THIN);//左边框
            style.setBorderTop(BorderStyle.THIN);//上边框
            style.setBorderRight(BorderStyle.THIN);//右边框
        	cell.setCellStyle(style);
        }
        style.setAlignment(HorizontalAlignment.CENTER);
    }

/*==========================================================咨询统计  加入创建人、创建时间、查询时间区间 =====================================================================*/

    /**
     * 根据List数据导出(双层表头)excel (包含创建人、创建时间、查询时间区间)  咨询统计
     * @param title
     * @param headers
     * @param fields
     * @param startRow
     * @param wb
     * @param data
     * @throws IOException
     */
    public static <T> void exportDoubleDeskHeaderExcel(String title, String[] head0, String[] headnum0,String[] head1,Integer startCell,Integer endCell,String[] fields, int startRow, Workbook wb, List<T> data,ExportParamVo exportParamVo) throws IOException {
    	if (data.size()>20000) {
    		LOGGER.info("警告：导出Excel时过多装载List数据，这样可能造成JVM内存溢出！！！");
		}
        Sheet sheet = null;
        startRow = startRow>0?startRow+4:startRow;
        int index = startRow, pageRowNo = startRow, columnCount = head0.length; // 行号、页码、列数
        Row nRow = null;

        if(!PublicUtil.isEmpty(data)) {
	        for (T obj : data) {
	            int sheetIndex = index/sheetMaxCount;
	            if (index % sheetMaxCount == 0) {
	                sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
	                sheet = wb.getSheetAt(sheetIndex);
	                sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
	                pageRowNo =	createDoubleHeader(sheet, title, head0,headnum0,head1,startCell,endCell,exportParamVo);
	            }else{
	                sheet = wb.getSheetAt(sheetIndex);
	            }
	            index++;
	            @SuppressWarnings("unchecked")
	            Map<String, Object> map = obj instanceof Map ? (Map<String, Object>) obj : beanToMap(obj);
	            nRow = sheet.createRow(pageRowNo++); // 新建行对象
	            for (int j = 0; j < columnCount; j++) {
	                setCellValue(sheet, nRow.createCell(j), map.get(fields[j]),(short)4);
	            }
	        }
        }else {
        	sheet = wb.createSheet(title + "_" + (1));
        }

    }

    /**
     * 创建双层表头 (包含创建人、创建时间、查询时间区间)  咨询统计
     * @param sheet
     * @param headers
     */
    private static int createDoubleHeader(Sheet sheet, String title, String[] head0, String[] headnum0,String[] head1,Integer startCell,Integer endCell,ExportParamVo exportParamVo){

    	int rowIndex = 0;
    	sheet.setDefaultColumnWidth(columnWidth);
    	//设置标题
        Row tRow = sheet.createRow(0);
        rowIndex++;
        Cell hc = tRow.createCell(0);
        for(int i = 1; i < head0.length; i++) {
        	Cell cell = tRow.createCell(i);
        	CellStyle style = sheet.getWorkbook().getCellStyleAt((short) 1);
        	style.setBorderRight(BorderStyle.THIN);
        	cell.setCellStyle(style);
        }
        hc.setCellValue(new XSSFRichTextString(title));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, head0.length-1));// 合并标题行：起始行号，终止行号， 起始列号，终止列号
        hc.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 1));

        //设置创建人、创建时间
        Row tRow1 = sheet.createRow(1);
        rowIndex++;
        Cell hc1 = tRow1.createCell(0);
        for(int i = 1; i < head0.length; i++) {
        	Cell cell = tRow1.createCell(i);
        	if(i==head0.length-1)
        	cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));
        }
        hc1.setCellValue("创建人："+exportParamVo.getCreater()+"  创建时间："+exportParamVo.getCreateTime());
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, head0.length - 1));// 合并标题行：起始行号，终止行号， 起始列号，终止列号
        hc1.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));

        //设置查询时间区间
        Row tRow2 = sheet.createRow(2);
        rowIndex++;
        Cell hc2 = tRow2.createCell(0);
        for(int i = 1; i < head0.length; i++) {
        	Cell cell = tRow2.createCell(i);
        	if(i==head0.length-1)
        	cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));
        }
        hc2.setCellValue("查询时间区间："+exportParamVo.getQueryStartTime()+" ~ "+exportParamVo.getQueryEndTime());
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, head0.length - 1));// 合并标题行：起始行号，终止行号， 起始列号，终止列号
        hc2.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));

        //第一行表头列名
        Row nRow = sheet.createRow(3);
        rowIndex++;
        for (int i = 0; i < head0.length; i++) {
        	Cell cell = nRow.createCell(i);
            cell = nRow.createCell(i);
            cell.setCellValue(head0[i]);
            cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 3));
        }

        //第二行表头列名
        Row row = sheet.createRow(4);
        rowIndex++;
        for (int i = 0; i < head0.length; i++) {
            if(i >= startCell && i< endCell) {
                for (int j = 0; j < head1.length; j++) {
                	Cell cell = row.createCell(i+j);
                    cell.setCellValue(head1[j]);
                    cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 3));
                }
                break;
            }
            Cell cell = row.createCell(i);
            cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));
        }

        //动态合并单元格
        for (int i = 0; i < headnum0.length; i++) {
            String[] temp = headnum0[i].split(",");
            Integer startrow = Integer.parseInt(temp[0])+2;
            Integer overrow = Integer.parseInt(temp[1])+2;
            Integer startcol = Integer.parseInt(temp[2]);
            Integer overcol = Integer.parseInt(temp[3]);
            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
                    startcol, overcol));
        }

        return rowIndex;
    }

/*=====================================预约投保综合统计  =========================================================*/
    /**
     * 根据List数据导出(双层表头)excel  (包含创建人、创建时间、查询时间区间)
     * @param title
     * @param headers
     * @param fields
     * @param startRow
     * @param wb
     * @param data
     * @throws IOException
     */
    public static <T> void exportDoubleDeskHeaderListExcel(String title, String[] head0, String[] headnum0,String[] head1,Integer startCell,Integer endCell, int startRow, Workbook wb, List<List<String>> data, ExportParamVo exportParamVo) throws IOException {
    	if (data.size()>20000) {
    		LOGGER.info("警告：导出Excel时过多装载List数据，这样可能造成JVM内存溢出！！！");
		}
        Sheet sheet = null;
        startRow = startRow>0?startRow+2:startRow;
        int index = startRow, pageRowNo = startRow, columnCount = head0.length; // 行号、页码、列数
        Row nRow = null;

        if(!PublicUtil.isEmpty(data)) {
	        for (List obj : data) {
	            int sheetIndex = index/sheetMaxCount;
	            if (index % sheetMaxCount == 0) {
	                sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
	                sheet = wb.getSheetAt(sheetIndex);
	                sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
	                pageRowNo = createDoubleHeader(sheet, title, head0,headnum0,head1,startCell,endCell,exportParamVo);
	            }else{
	                sheet = wb.getSheetAt(sheetIndex);
	            }
	            index++;
	            /*@SuppressWarnings("unchecked")
	            Map<String, Object> map = obj instanceof Map ? (Map<String, Object>) obj : beanToMap(obj);  */

	            nRow = sheet.createRow(pageRowNo++); // 新建行对象
	            for (int j = 0; j < columnCount; j++) {
	                setCellValue(sheet, nRow.createCell(j),obj.get(j),(short)4);
	            }
	        }
        }else {
        	sheet = wb.createSheet(title + "_" + (1));
        }

    }

    /*=====================================质检清单报表  =========================================================*/
    /**
     * 根据List数据导出excel  (包含创建人、创建时间、查询时间区间)
     * @param title
     * @param head0
     * @param startRow
     * @param wb
     * @param data
     * @throws IOException
     */
    public static <T> void exportExcelForQuality(String title, String[] head0,int startRow,Workbook wb, List<List<Object>> data, ExportParamVo exportParamVo) throws IOException {
    	if (data.size()>20000) {
    		LOGGER.info("警告：导出Excel时过多装载List数据，这样可能造成JVM内存溢出！！！");
		}
        Sheet sheet = null;
        startRow = startRow>0?startRow+2:startRow;
        int index = startRow, pageRowNo = startRow, columnCount = head0.length; // 行号、页码、列数
        Row nRow = null;

        if(!PublicUtil.isEmpty(data)) {
	        for (List obj : data) {
	            int sheetIndex = index/sheetMaxCount;
	            if (index % sheetMaxCount == 0) {
	                sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
	                sheet = wb.getSheetAt(sheetIndex);
	                sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
	                pageRowNo = createHeader(sheet, title, head0,exportParamVo);
	            }else{
	                sheet = wb.getSheetAt(sheetIndex);
	            }
	            index++;
	            nRow = sheet.createRow(pageRowNo++); // 新建行对象
	            for (int j = 0; j < columnCount; j++) {
	                setCellValue(sheet, nRow.createCell(j),obj.get(j),(short)4);
	            }
	        }
        }else {
        	sheet = wb.createSheet(title + "_" + (1));
        }
    }

        /**
         * 根据ResultSet数据导出excel
         * @param title
         * @param headers
         * @param fields
         * @param startRow
         * @param wb
         * @param rs
         * @throws Exception
         */
        public static <T> void exportExcelByResultSet(String title, String[] headers, String[] fields, int startRow, Workbook wb, ResultSet rs,ExportParamVo exportParamVo) throws Exception {
        	Sheet sheet = null;
            startRow = startRow>0?startRow+2:startRow;
            int index = startRow, pageRowNo = startRow, columnCount = headers.length; // 行号、页码、列数
            Row nRow = null;
            ResultSetMetaData rsm = rs.getMetaData();
            int rsColumnCount = rsm.getColumnCount();// ResultSet列数
            Map<String, Object> obj_map = new HashMap<String, Object>(0);
            if(rs.isBeforeFirst()) {
	            while(rs.next()) {//遍历游标
	            	for (int i = 1; i <= rsColumnCount; i++) {//转换rs到map便于提取
	    				obj_map.put(rsm.getColumnName(i).toLowerCase(), rs.getObject(i));
	    			}
	                int sheetIndex = index/sheetMaxCount;
	                if (index % sheetMaxCount == 0) {
	                    sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
	                    sheet = wb.getSheetAt(sheetIndex);
	                    sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
	                    pageRowNo = 2;
	                    createHeader(sheet, title, headers,exportParamVo);
	                }else{
	                    sheet = wb.getSheetAt(sheetIndex);
	                }
	                index++;
	                nRow = sheet.createRow(pageRowNo++); // 新建行对象

	                for (int j = 0; j < columnCount; j++) {//正式写入字段，梳理顺序为传入字段顺序
	                    setCellValue(sheet, nRow.createCell(j) , obj_map.get(fields[j]),(short)4);
	                }
	                obj_map.clear();//清空map待用
	            }
            }else {
            	sheet = wb.createSheet(title + "_" + (1));
            }
    }
        
	// -------------------------------------
	/**
	 * 
	 * @author WANGCONG 
	 * 2022年2月7日上午10:46:08
	 * @desc 医护类卡券模板下载
	 */
	public static <T> void downLoadMedicalTemplate(String title, String[] headers, String[] fields, int startRow,
			Workbook wb, List<T> data) throws IOException {
		Sheet sheet = null;
		startRow = startRow > 0 ? startRow + 2 : startRow;
		int index = startRow; // 行号
		int sheetIndex = index/sheetMaxCount;
		sheet = wb.createSheet(title);
        sheet = wb.getSheetAt(sheetIndex);
        sheet.setDisplayGridlines(true);// 设置表标题是否有表格边框
        sheet.setDefaultColumnWidth(15);
        //设置表头
        Row nRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = nRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(sheet.getWorkbook().getCellStyleAt((short) 2));
        }
	}    

	
	/**
	 * 
	 * @author R 
	 * 2022年02月24日 16:29:34
	 * @desc 服务包配置下载
	 */
	public <T> void downLoadPackageConfig(String title, String[] headers,
			Workbook wb, List<StorePackageConfigEntity> packageConfigEntityList, Map<String,List<StoreActivityConfigEntity>> activityConfigEntityMap) throws IOException {
		
		Sheet sheet = null;
		//创建表头
		int index = 0; // 行号
		int sheetIndex = index/sheetMaxCount;
		sheet = wb.createSheet(title);
        sheet = wb.getSheetAt(sheetIndex);
        sheet.setDisplayGridlines(true);// 设置表标题是否有表格边框
        sheet.setDefaultColumnWidth(15);
        //设置表头
        Row nRow = sheet.createRow(0);
        
        CellStyle headCellStyle=wb.createCellStyle(); // 创建单元格样式
        headCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headCellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置单元格水平方向对其方式
        headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置单元格垂直方向对其方式
        headCellStyle.setBorderRight(BorderStyle.THIN);
        headCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        headCellStyle.setBorderLeft(BorderStyle.THIN);
        headCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        headCellStyle.setBorderTop(BorderStyle.THIN);
        headCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        headCellStyle.setBorderBottom(BorderStyle.THIN);
        headCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        Font headFont = wb.createFont();
        headFont.setBold(true);
        headFont.setColor(IndexedColors.BLACK.getIndex());
        headCellStyle.setFont(headFont);
        
        for (int i = 0; i < headers.length; i++) {
            Cell cell = nRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headCellStyle);
        }
        
        
        CellStyle contentCellStyle=wb.createCellStyle(); // 创建单元格样式
        contentCellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置单元格水平方向对其方式
        contentCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置单元格垂直方向对其方式
        
        int rowNum = 0;
		//遍历循环数据
		for (int i = 0; i < packageConfigEntityList.size(); i++) {
			StorePackageConfigEntity storePackageConfigEntity = packageConfigEntityList.get(i);
			//获取服务包配置产品编码
			String configCode = storePackageConfigEntity.getConfigCode();
			//获取该服务包配置下的所有服务内容
			List<StoreActivityConfigEntity> activityConfigList = activityConfigEntityMap.get(configCode);
			
			if(activityConfigList.size()>1){
				// CellRangeAddress 四个参数 起始行,结束行,起始列,结束列
				//服务包配置编码单元格
				CellRangeAddress configCodeCellRangeAddress = new CellRangeAddress(rowNum+1,rowNum+activityConfigList.size(),0,0);
				//服务包配置名称单元格
				CellRangeAddress configNameCellRangeAddress = new CellRangeAddress(rowNum+1,rowNum+activityConfigList.size(),1,1);
				//服务包配置价格单元格
				CellRangeAddress priceCellRangeAddress = new CellRangeAddress(rowNum+1,rowNum+activityConfigList.size(),2,2);
				//服务包配置状态单元格
				CellRangeAddress statusCellRangeAddress = new CellRangeAddress(rowNum+1,rowNum+activityConfigList.size(),3,3);
			
				sheet.addMergedRegion(configCodeCellRangeAddress);
				sheet.addMergedRegion(configNameCellRangeAddress);
				sheet.addMergedRegion(priceCellRangeAddress);
				sheet.addMergedRegion(statusCellRangeAddress);
			}
			
			for (int j = 0; j < activityConfigList.size(); j++) {
				StoreActivityConfigEntity activityConfigEntity = activityConfigList.get(j);
				Row row = sheet.createRow(rowNum+1+j);
				if(j==0){
					Cell cellA = row.createCell(0);
					cellA.setCellStyle(contentCellStyle);
					cellA.setCellValue(storePackageConfigEntity.getConfigCode());
					Cell cellB = row.createCell(1);
					cellB.setCellStyle(contentCellStyle);
					cellB.setCellValue(storePackageConfigEntity.getConfigName());
					Cell cellC = row.createCell(2);
					cellC.setCellStyle(contentCellStyle);
					cellC.setCellValue(storePackageConfigEntity.getPackageType());
					Cell cellD = row.createCell(3);
					cellD.setCellStyle(contentCellStyle);
					cellD.setCellValue(storePackageConfigEntity.getStatus());
					Cell cellE = row.createCell(4);
					cellE.setCellStyle(contentCellStyle);
					cellE.setCellValue(activityConfigEntity.getActivityCode());
					Cell cellF = row.createCell(5);
					cellF.setCellStyle(contentCellStyle);
					cellF.setCellValue(activityConfigEntity.getActivityName());
					Cell cellG = row.createCell(6);
					cellG.setCellStyle(contentCellStyle);
					cellG.setCellValue(activityConfigEntity.getCount());
					Cell cellH = row.createCell(7);
					cellH.setCellStyle(contentCellStyle);
					cellH.setCellValue(activityConfigEntity.getValidityPeriod()+"天");
					Cell cellI = row.createCell(8);
					cellI.setCellStyle(contentCellStyle);
					cellI.setCellValue(activityConfigEntity.getFormCode());
					Cell cellJ = row.createCell(9);
					cellJ.setCellStyle(contentCellStyle);
					cellJ.setCellValue(activityConfigEntity.getUsageCount());
					Cell cellK = row.createCell(10);
					cellK.setCellStyle(contentCellStyle);
					cellK.setCellValue(activityConfigEntity.getProviderCN());
				}else{
					Cell cellE = row.createCell(4);
					cellE.setCellStyle(contentCellStyle);
					cellE.setCellValue(activityConfigEntity.getActivityCode());
					Cell cellF = row.createCell(5);
					cellF.setCellStyle(contentCellStyle);
					cellF.setCellValue(activityConfigEntity.getActivityName());
					Cell cellG = row.createCell(6);
					cellG.setCellStyle(contentCellStyle);
					cellG.setCellValue(activityConfigEntity.getCount());
					Cell cellH = row.createCell(7);
					cellH.setCellStyle(contentCellStyle);
					cellH.setCellValue(activityConfigEntity.getValidityPeriod()+"天");
					Cell cellI = row.createCell(8);
					cellI.setCellStyle(contentCellStyle);
					cellI.setCellValue(activityConfigEntity.getFormCode());
					Cell cellJ = row.createCell(9);
					cellJ.setCellStyle(contentCellStyle);
					cellJ.setCellValue(activityConfigEntity.getUsageCount());
					Cell cellK = row.createCell(10);
					cellK.setCellStyle(contentCellStyle);
					cellK.setCellValue(activityConfigEntity.getProviderCN());
				}
			}
			
//			int providerCount = 0 ;
//			String tempProvider = "";
//			for (int j = 0; j < activityConfigList.size(); j++) {
//				StoreActivityConfigEntity currentActivityConfigEntity = activityConfigList.get(j);
//				if(PublicUtil.isEmpty(tempProvider)){
//					tempProvider = currentActivityConfigEntity.getProvider();
//				}else{
//					if(!tempProvider.equals(currentActivityConfigEntity.getProvider())){
//						// CellRangeAddress 四个参数 起始行,结束行,起始列,结束列
//						//服务商单元格
//						CellRangeAddress providerCellRangeAddress = new CellRangeAddress(rowNum+1+providerCount,rowNum+j,9,9);
//						sheet.addMergedRegion(providerCellRangeAddress);
//						
//						tempProvider = currentActivityConfigEntity.getProvider();
//						providerCount = j ;
//					}else{
//						if(j==(activityConfigList.size()-1)){
//							// CellRangeAddress 四个参数 起始行,结束行,起始列,结束列
//							//服务商单元格
//							CellRangeAddress providerCellRangeAddress = new CellRangeAddress(rowNum+1+providerCount,rowNum+1+j,9,9);
//							sheet.addMergedRegion(providerCellRangeAddress);
//						}
//					}
//				}
//			}
			
			
			
			if(i<packageConfigEntityList.size()){
				 CellStyle nullCellStyle=wb.createCellStyle(); // 创建单元格样式
				 nullCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
				 nullCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
				
				//插入空行
				Row nullRow = sheet.createRow(rowNum+1+activityConfigList.size());
				for (int j = 0; j < 11; j++) {
					Cell cell = nullRow.createCell(j);
					
					cell.setCellStyle(nullCellStyle);
				}
			}
			//下次合并 起始行数
			rowNum += (activityConfigList.size()+1);
		}
	} 
}
